select Sno, Sname
from student


select Sname, 2010-Sage
from student


select Sname as Name, 2010-Sage as Birthday
from student

select Sname Name, 2010-Sage Birthday
from student

select * from student;

select Sno,Sname,Ssex,Sage,Sdept
from student

select Sno, Sname
from student
where Sdept='CS'


select distinct (Sname, Ssex), Sage
from student


select distinct Sname, Ssex, Sage
from student

Select Sno from SC;


Select distinct Sno from SC;


select Sno, Sname
from student
where Sage<20

select distinct Sno
from course
where grade<60


select Sno, Sname
from student
where Sage between 20 and 23;

select Sno, Sname
from student
where Sage>=20
      and Sage<=23


select Sno, Sname
from student
where Sdept in ('IS', 'MA', 'CS')

select Sno, Sname
from student
where Sdept='IS'
      or Sdept='MA'
      or Sdept='CS'

select Sno, Sname
from student
where (Sdept='IS'
      or Sdept='MA'
      or Sdept='CS')
      and Sage<=20

select Sno, Sname
from student
where Sno like '99_%'

select Sno, Sname
from student
where Sno like '99|_%' escape '|'








select Sno,Sname
from student
where scome='2010-03-01'

select Sno,Sname
from student
where scome=date('2010-03-01','yyyy-MM-dd')

select Sno, Sname from student
where date(scome)=date('2010-03-01', 'yyyy-MM-dd')

select Sno, Sname from student
where scome>date('2010-02-28', 'yyyy-MM-dd')
and scome<date('2010-03-02','yyyy-MM-dd')





DELIMITER $$
CREATE PROCEDURE MyProcedure(
	IN p_accountOid varchar(36),
	IN p_buId varchar(36),
    OUT p_count int)    
    BEGIN
	   -- insert....
	   -- update....       
    END$$
DELIMITER ;



DELIMITER $$

CREATE FUNCTION MyFunction()
    RETURNS int
    BEGIN
		-- update
		-- insert
		return 3;
    END$$
DELIMITER ;




DELIMITER $$

CREATE TRIGGER MyTrigger BEFORE/AFTER INSERT/UPDATE/DELETE
    ON student
    FOR EACH ROW BEGIN
		-- insert
		-- update
    END$$

DELIMITER ;


CREATE SEQUENCE studentno_sequence
    increment by 1
    start with 1
    nomaxvalue
    nocycle
    cache 10;

select studentno_sequence.CURRVAL;
select studentno_sequence.NEXTVAL;


create snapshot student1
refresh force start with sysdate+1/1440 
next sysdate+1 
as select * from student; 



select a.Sno, a.Sname, b.Cno, b.Grade
from student a, CS b
where a.Sno=b.Sno

select a.Sno, a.Sname, b.Cno, b.Grade
from student a
inner join CS b on (a.Sno=b.Sno)

select distinct a.Sno, a.Sname
from student a
inner join CS b on (a.Sno=b.Sno)
where b.Grade>80



select distinct a.Sno, a.Sname
from student a, CS b
where a.Sno=b.Sno 
and b.Grade>80


select a.Sname, b.Cno, b.Grade
from student a, CS b
where a.Sno=b.Sno(*) 

select a.Sno, b.Cno, b.Grade
from student a
left join CS b on (a.Sno=b.Sno)


select a.Sname, b.Cname
from student a
join Course b

select a.Sname, b.Cname, c.Grade
from student a
join Course b
left join SC c on (a.Sno=c.Sno and b.Cno=c.Cno)


select t.bCname
from (
select a.Sname, b.Cname, ifnull(c.Grade, 0)
from student a
join Course b
left join SC c on (a.Sno=c.Sno and b.Cno=c.Cno)
    ) t
group by b.Cname





select a.CName, b.Cname as p_Cname
from Course a
join Course b on (a.cpno=b.cno)


select a.CName, b.Cname as p_Cname
from Course a
left join Course b on (a.cpno=b.cno)



select Sname, Sage
from student
order by sage asc



select Sdept, Sname, Sage
from student
order by Sdept desc, Sage asc

select count(1)
from student


select sum(Sage)
from student;



select Sdept, avg(Sage)
from student
group by Sdept

select a.Sname, avg(b.Grade) as avgGrade
from student a
join SC b on (a.Sno = b.Sno)
group by a.Sname


select a.Sno, a.Sname, avg(b.Grade) as avgGrade
from student a
join SC b on (a.Sno = b.Sno)
order by b.Grade>60
group by a.Sno, a.Sname


having avgGrade>60

o

select a.Sno,  avg(ifnull(b.Grade, 0)) as avgGrade
from student a
left join SC b on (a.Sno = b.Sno)
where b.Grade>60
group by a.Sno
order by avgGrade desc


select a.Sno, a.Sage, avg(b.Grade) as avgGrade
from student a
join SC b on (a.Sno = b.Sno)
group by a.Sno



select a.Sno, a.Sage, avg(b.Grade) as avgGrade
from student a
join SC b on (a.Sno = b.Sno)
group by a.Sno, a.Sage


select a.Sno, a.Sname
from student a
where a.Sno not in (
    select Sno from SC)

select distinct a.Sno, a.Sname
from student a
left join SC b on (a.Sno=b.Sno)
where b.Cno is null

select a.Sno, a.Sname
from student a
join 




select a.Sno, a.Sname, sum(ifnull(b.Grade, 0)) as sumGrade
from student a
left join SC b on (a.Sno = b.Sno)
group by a.Sno, a.Sname



select a.Sno, a.Sname, ifnull(b.Grade, 0) as sumGrade
from student a
left join (
    select sub.Sno Sno, sum(sub.Grade) Grade from SC sub group by sub.Sno
    )b on (a.Sno = b.Sno)



select a.Sno, a.Sname, 
(select sum(b.Grade) from SC b where b.Sno=a.Sno) as sumGrade
from student a

select c.Cname,
( select a.Sname from 
) as Sname
from Course c

select s.Sdept, s.Sname, tmp.maxAge
from student s
join (select  a.Sdept, max(a.Sage) as maxAge 
from student a
group by  a.Sdept) tmp on (s.Sdept=tmp.Sdept and s.Sage=tmp.maxAge)


select t.*
from (select a.*, rownum as rowNumber
      from a.student) t
where t.rowNumber>=21
and t.rowNumber<=30



select a.*
from student a
limit 21, 30



SELECT TOP 10 * 
FROM student 
WHERE (Sno NOT IN (SELECT TOP 30 Sno FROM student ORDER BY Sno DESC)) 
ORDER BY Sno DESC


update student
set Sage=Sage+1


update student s, SC b
set s.Sdept='A'
where s.Sno=b.Sno


update student s,
(select b.Sno, sum(b.grade)  as Grade
from SC b
group by b.Sno) t
set s.total=t.Grade
where s.Sno=t.Sno

update student s
set s.total=(
    select sum(b.Grade) 
    from SC b where b.Sno=s.Sno
)
